package cn.shujuhai.cms.mapper;

import cn.shujuhai.cms.domain.SysChannel;
import com.github.yulichang.base.MPJBaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * @Author: zhangDeZhong
 * @Date: 2024/12/5 12:01
 * @Description: 针对表【sys_channel(栏目表)】的数据库操作Mapper
 **/
@Mapper
public interface SysChannelMapper extends MPJBaseMapper<SysChannel> {
    /**
     * 计算 count 字段的总和
     *
     * @return count 字段的总和
     */
    @Select("SELECT SUM(count) FROM sys_channel")
    Long sumCount();

    /**
     * 查询父栏目及其文章的浏览量，并计算父栏目及其子栏目的总 count 值
     *
     * @return 列表，包含栏目名称、总浏览量（包括子栏目）和文章浏览量
     */
    @Select("SELECT sc.channel_name, " +
            "sc.count + COALESCE(SUM(c.count), 0) AS channel_total_count, " +
            "CASE WHEN COUNT(c.channel_id) > 0 THEN " +
            "     ROUND((COALESCE(SUM(ia.count), 0) + COALESCE(SUM(child_counts.child_count), 0)) / COUNT(c.channel_id)) " +
            "ELSE ROUND(COALESCE(SUM(ia.count), 0) + COALESCE(SUM(child_counts.child_count), 0)) END AS article_count " +
            "FROM sys_channel sc " +
            "LEFT JOIN info_article ia ON sc.channel_id = ia.channel_id AND ia.deleted = 0 " +
            "LEFT JOIN sys_channel c ON sc.channel_id = c.parent_id AND c.deleted = 0 " +
            "LEFT JOIN (SELECT c.parent_id, SUM(ia.count) AS child_count " +
            "           FROM sys_channel c " +
            "           LEFT JOIN info_article ia ON c.channel_id = ia.channel_id AND ia.deleted = 0 " +
            "           WHERE c.deleted = 0 " +
            "           GROUP BY c.parent_id) child_counts ON sc.channel_id = child_counts.parent_id " +
            "WHERE sc.parent_id = 0 AND sc.deleted = 0 AND sc.channel_name <> '全站检索' " +
            "GROUP BY sc.channel_id, sc.channel_name, sc.count " +
            "ORDER BY sc.channel_sort ASC")
    List<Map<String, Object>> selectChannelAndArticleViews();
}




